package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import org.joda.time.DateTime;
import org.joda.time.base.BaseDateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import model.ProduccionBase;
import model.Pudin;
import model.ProduccionBase.TipoBase;
import model.Pudin.BaseSabor;
import model.Pudin.PesoForma;



public class LocalDatabase {

	Connection conn = null;
	
	public LocalDatabase() {
	
		
		try {
			prepareConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public void prepareConnection() throws Exception {
		
		String userName = "root";
		String password = "root";
		String url = "jdbc:mysql://127.0.0.1/postresely";
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection(url, userName, password);
		
	}
	
	public int getGramaje(Pudin.BaseSabor baseSabor, Pudin.PesoForma pesoForma) {
		int gramaje = 0;
		// TODO implemenar llamado a base de datos
		return gramaje;
	}
	
	public ArrayList<Integer> getCodigos(ProduccionBase.TipoBase tipoBase, String baseSabor) {
		
		ArrayList<Integer> listaDeCodigos = new ArrayList<Integer>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT codigoarticulo FROM postres WHERE base = '" + tipoBase.name().toUpperCase() +"'" +
					" AND sabor = '" + baseSabor.toUpperCase() + "' ORDER BY codigoarticulo ASC";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int codigo = rs.getInt("codigoarticulo");

				listaDeCodigos.add(codigo);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listaDeCodigos;
	}
	
	public ArrayList<String> getTamanos(ProduccionBase.TipoBase tipoBase, String baseSabor) {
		
		ArrayList<String> listaDeTamanos = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT tamano, forma FROM postres WHERE base = '" + tipoBase.name().toUpperCase() +"'" +
					" AND sabor = '" + baseSabor.toUpperCase() + "' ORDER BY codigoarticulo ASC";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				String tamano = rs.getString("tamano");
				String forma = rs.getString("forma");

				listaDeTamanos.add(tamano + "_" + forma);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listaDeTamanos;
	}
	
	
	public ArrayList<Integer> getPesos(ProduccionBase.TipoBase tipoBase, String baseSabor) {
			
			ArrayList<Integer> listaDePesos = new ArrayList<Integer>();
	
			try {
	
				//prepareConnection();
	
				Statement s = conn.createStatement();
				String query = "SELECT peso FROM postres WHERE base = '" + tipoBase.name().toUpperCase() +"'" +
						" AND sabor = '" + baseSabor.toUpperCase() + "' ORDER BY codigoarticulo ASC";
				
				s.executeQuery(query);
	
				ResultSet rs = s.getResultSet();
	
				while (rs.next()) {
	
					int peso = rs.getInt("peso");
	
					listaDePesos.add(peso);
	
				}
				rs.close();
				s.close();
	
			} catch (Exception e) {
				System.err.println("Cannot connect to database server");
				e.printStackTrace();
			} finally {
				//closeConnection();
			}
	
			return listaDePesos;
	}

	public void insertInventarioAdelanto(String sabor, String tamano, int inventario, int adelanto, DateTime dia, int codigo) {
		
		ArrayList<Integer> listaDePesos = new ArrayList<Integer>();
		
		 DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
		 String timeStamp = fmt.print(dia);


	
		try {
	
			//prepareConnection();
	
			Statement s = conn.createStatement();
			String query = "SELECT id FROM inventario_adelanto WHERE codigo = " + codigo + " AND fecha = '" + timeStamp + "'";
			
			s.executeQuery(query);
	
			ResultSet rs = s.getResultSet();
	
			boolean updated =  false;
			if (rs.next()) {
	
				int id = rs.getInt("id");
	
				
				String statement = "UPDATE inventario_adelanto SET inventario=" + inventario
						+ ", adelanto=" + adelanto + " WHERE id=" + id;
				rs.close();
				s.executeUpdate(statement);
	
				updated = true;
			}
			
			if(!updated) {
				query = "INSERT INTO inventario_adelanto (base_sabor, tamano, fecha,inventario,adelanto,codigo"
						+ ") VALUES ('" + sabor.toUpperCase() + "','"+tamano+"','"+timeStamp+"',"+inventario+","+adelanto+","+Integer.valueOf(codigo)+")";
				
				s.executeUpdate(query);
			}
			
			rs.close();
			s.close();
	
		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	
	}
	
	public int getInventario(int codigo, String dia) {
		
		int inventario = 0;
		
		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT inventario FROM inventario_adelanto WHERE codigo = " + codigo + " AND fecha = '" + dia + "'";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			
			if (rs.next()) {

				inventario = rs.getInt("inventario");

			}
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return inventario;
}
	
	public int getAdelanto(int codigo, String dia) {
			
			int adelanto = 0;
	
			try {
	
				//prepareConnection();
	
				Statement s = conn.createStatement();
				String query = "SELECT adelanto FROM inventario_adelanto WHERE codigo = " + codigo + " AND fecha = '" + dia + "'";
				
				s.executeQuery(query);
	
				ResultSet rs = s.getResultSet();
	
				
				if (rs.next()) {
	
					adelanto = rs.getInt("adelanto");
	
				}
				
				rs.close();
				s.close();
	
			} catch (Exception e) {
				System.err.println("Cannot connect to database server");
				e.printStackTrace();
			} finally {
				//closeConnection();
			}
	
			return adelanto;
	}

}
